IF OBJECT_ID('dbo.RptAwardsBookedReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAwardsBookedReport
    IF OBJECT_ID('dbo.RptAwardsBookedReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAwardsBookedReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAwardsBookedReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAwardsBookedReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 6/19/2007
--
-- Description: Source of Report, "AwardsBookedReport"
--                  In Insight DB Reporting Services
--
--   Awards Booked Under 75% F&A, 26% Offsite; 25% Clinical Report
--        This report documents all awards that were booked with an 
--        indirect cost rate that is not consistent with our current 
--        DHHS negotiated rates.
--
-- Parameters:  
--     @StartDate                         
--     @EndDate                           
--     @Institution                       
--     @Department                        
--     @PrincipalInvestigator                        
--     @Sponsor                           
--     @PostAwardManager                         
--     @domainUserId                      
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAwardsBookedReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/19/07     KC        Created
-- 7/26/07     KC        Changed Naming Conventions, readied for QA
-- 7/20/07     KC        Added PS.stat_type = 1 to dbo.InfoEdPropStat JOIN 
--                       And Added PostAwardManagerName
-- 8/2/07      KC        Added QA Feedback, BudgetDates fixes
-- 8/7/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 9/6/07      KC        Changed to new source tables
-- 9/18/07     KC        Added @PreAwardManager as Param.
--                       Fixed check for 25,26 and 75 IDCRate.
--                       Fixed SponsorAgreementID
-- 9/21/07     KC        Added @Unit
-- 9/25/07     KC        Used ID for Institution, Dept, Unit
-- 9/28/07     KC        Made PROJ an INNER JOIN, Fixed ProjectStartDate
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 10/24/07    KC        Added ignore for SponsorType of 'Sundry'
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptAwardsBookedReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL,             
     @Sponsor                           VARCHAR(100) = NULL,
     @PreAwardManager                   VARCHAR(100) = NULL,
     @PostAwardManager                  VARCHAR(100) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT,
               @PISearchManagerName    VARCHAR(50)
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @PreAwardManager IN ('ALL','','0') SET @PreAwardManager = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 
     
     -- Result Set
     SELECT    AI.FolderNumber AS ProposalNumber,        
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.ProposalTitle AS ProposalTitle, 
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS UnitCode,
               AI.SponsorName AS SponsorName,        
               AI.SponsorAgreementID AS SponsorNumber,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               BUDG.BudgetViewType AS BudgetPeriodStatus,
               AI.PostAwardManagerCode AS PostAwardManager,
               ISNULL(AI.PostAwardManagerName,' Post Award Manager Not Assigned') AS PostAwardManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,' Pre Award Manager Not Assigned') AS PreAwardManagerName,
               PROJ.ProjectDirectCost AS DirectCostforProjectPeriod,
               BUDG.BudgetDirectCost AS DirectCostforBudgetPeriod,
               PROJ.ProjectIndirectCost AS FAforProjectPeriod,
               BUDG.BudgetIndirectCost AS FAforBudgetPeriod,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,
               PROJ.IndirectCostRate AS ProjectIndirectCostRate,
               BUDG.IndirectCostRate AS BudgetIndirectCostRate,
               AI.ProposalStatus  AS ProposalStatus,
               AI.InstrumentType AS ProposalType,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.DeadlineDate AS SponsorDueDate,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.SponsorId AS SponsorId,
               AI.ResourceId AS AgrResourceId
     FROM      dbo.AgrAgreement_Info AI INNER JOIN 

               -- ProjectDirectCosts have to be summed from the Budget tables
               -- Over the life of the Agreement (Project)
               dbo.vwAgreementProjectBudgets PROJ ON AI.AgreementId = PROJ.AgreementId LEFT OUTER JOIN
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN

               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
               
     WHERE     (AI.ProjectStartDate BETWEEN @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor ))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@PostAwardManager IS NULL OR AI.PostAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PostAwardManager )))  AND
               
               -- Awards Booked Under 75% F&A, 26% Offsite; 25% Clinical Report
               (PROJ.IndirectCostRate NOT IN (25, 26, 76)) AND

               -- Ignore SponsorType SUNDRY
               (RTRIM(AI.SponsorTypeName) != 'Sundry')
     ORDER BY AI.SponsorName, AI.Institution, AI.Department

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptAwardsBookedReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAwardsBookedReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAwardsBookedReport >>>'
go

GRANT EXECUTE ON dbo.RptAwardsBookedReport TO InsightReport,Insight
go
